package com.hz.framework.MyBean;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;

import com.hz.framework.dao.DBQ;
import com.hz.sessionFactory.HibernateSessionFactory;

public class PageQuery {
	private List lstValue=null;
	private Session session=null;
	private DBQ dbq=null;
//	hql分页方法获得条数
	public int getRowCountHql(String Hql){
		session = HibernateSessionFactory.getSession();
		int count =0;
		try {
			int u = Hql.toUpperCase().indexOf("FROM ");
			String usql="";
			if(Hql.toUpperCase().lastIndexOf("ORDER ")!=-1){
				usql = Hql.substring( u ,Hql.toUpperCase().lastIndexOf("ORDER "));
			}else{
				usql = Hql.substring(u);
			}
			Hql = "select count(*) as cout "+usql;
			List list = session.createQuery(Hql).list();
			if(list!=null){
				if(list.size()>1){
					count = list.size();
				}else{
					count = Integer.parseInt(list.get(0).toString());
				}
			}
			return count;
		} catch (Exception e) {
			System.err.println("Exception on getRowCountHql method" + e.toString());
			System.out.println("Hql:"+Hql);
			return count;
		}finally{
			session.close();
		}
	}
//	sql分页方法获得条数
	public int getRowCountSql(String Sql){
		session = HibernateSessionFactory.getSession();
		int count =0;
		try {
			int u = Sql.toUpperCase().indexOf("FROM ");
			String usql="";
			if(Sql.toUpperCase().lastIndexOf("ORDER ")!=-1){
				usql = Sql.substring( u , Sql.toUpperCase().lastIndexOf("ORDER "));
			}else{
				usql = Sql.substring(u);
			}
			Sql = "select count(*) as cout "+usql;
			List list = session.createSQLQuery(Sql).list();
			if(list!=null){
				if(list.size()>1){
					count = list.size();
				}else{
					count = Integer.parseInt(list.get(0).toString());
				}
			}
			return count;
		} catch (Exception e) {
			System.err.println("Exception on getRowCountSql method" + e.toString());
			System.out.println("Sql:"+Sql);
			return count;
		}finally{
			session.close();
		}
	}
	
//	hql分页方法hql语句,开始数,最大数
	public List queryHqlList(String hql,int first,int max){
		lstValue = new ArrayList();
		try{
			session = HibernateSessionFactory.getSession();
			Query qu = session.createQuery(hql);
			qu.setFirstResult(first);
			//qu.setFetchSize(arg0); 每次取数据数
			qu.setMaxResults(max);
	    	lstValue=qu.list();
			return lstValue;
		}catch(Exception e){
			//e.printStackTrace();
			return null;
		}finally{
			session.close();
		}
	}
//	sql分页方法hql语句,开始数,最大数
	public List querySqlList(String sql,int first,int max){
		lstValue = new ArrayList();
		try{
			session = HibernateSessionFactory.getSession();
			Query qu = session.createSQLQuery(sql);
			qu.setFirstResult(first);
			//qu.setFetchSize(arg0); 每次取数据数
			qu.setMaxResults(max);
	    	lstValue=qu.list();
			return lstValue;
		}catch(Exception e){
			//e.printStackTrace();
			return null;
		}finally{
			session.close();
		}
	}
//	jdbc分页方法获得条数
	public int getRowCountJdbc(String Sql){
		int count =0;
		try {
			dbq = new DBQ();
			int u = Sql.toUpperCase().indexOf("FROM ");
			String usql="";
			if(Sql.toUpperCase().lastIndexOf("ORDER ")!=-1){
				usql = Sql.substring( u ,Sql.toUpperCase().lastIndexOf("ORDER "));
			}else{
				usql = Sql.substring(u);
			}
			Sql = "select count(*) as cout "+usql;
			ResultSet rs = dbq.getResults(Sql);
			if(rs.next()){
				count = rs.getInt(1);
			}
			return count;
		} catch (Exception e) {
			System.err.println("Exception on getRowCountHql method" + e.toString());
			System.out.println("Hql:"+Sql);
			return count;
		}finally{
			dbq.close();
		}
	}
//	jdbc分页方法Sql语句,开始数,最大数
	public List queryJdbcList(String Sql,int first,int max){
		dbq=new DBQ();
		lstValue = new ArrayList();
        ResultSet rs;
		try {
			PreparedStatement pst = dbq.getPreparedStatement(Sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = pst.executeQuery();
			if(rs.next()){
				if(0==first){
					rs.beforeFirst();
				}else{
					rs.relative(first-1);
				}
				int j=0;
		        ResultSetMetaData rsmd = rs.getMetaData();
		        while(rs.next() && j<max){
		            Map map = new HashMap();
					for(int i=1;i<=  rsmd.getColumnCount();i++){
					    String value = (String) rs.getString(i);
					    if("null".equalsIgnoreCase(value))value="";
						map.put(rsmd.getColumnLabel(i),value);
//						System.out.println(rsmd.getColumnLabel(i)+":"+value);
					}
					lstValue.add(map);
					j++;
		        }
			}
	        return lstValue;
		} catch (Exception e) {
			System.out.println(" List queryJdbcList 方法异常");
			e.printStackTrace();
			return null;
		}finally{
			dbq.close();
		}
	}
	
	
	public static void main(String[] args) {
		
	}
}
